﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;


namespace BoutiquePOS.DAL.Gateway
{
    public class SalesGateway : DbGateway
    {
        private List<SalesDtls> saless;
        SalesDtls aSales;
        //Sales aaSales;    
        //private SalesDtls sales;

        public void SaveSales(List<SalesDtls> aSalesDtls)
        {
            try
            {
                foreach (SalesDtls salesDtlse in aSalesDtls)
                {
                    ConnectionObj.Open();
                    string query = String.Format("INSERT INTO PosSalesDtls VALUES(@salesId,@paymentMode,@productId,@salesQty,@tradePrice,@mrp,@totalMRP,@discountPer,@discountAmt,@salesFreeQty)");
                    CommandObj.Parameters.Clear();
                    CommandObj.CommandText = query;
                    CommandObj.Parameters.Add("@salesId", SqlDbType.Int);
                    CommandObj.Parameters["@salesId"].Value = salesDtlse.SalesId;
                    CommandObj.Parameters.Add("@paymentMode", SqlDbType.Int);
                    CommandObj.Parameters["@paymentMode"].Value = salesDtlse.PaymentMode;
                    CommandObj.Parameters.Add("@productId", SqlDbType.Int);
                    CommandObj.Parameters["@productId"].Value = salesDtlse.AProduct.ProductId;
                    CommandObj.Parameters.Add("@salesQty", SqlDbType.Int);
                    CommandObj.Parameters["@salesQty"].Value = salesDtlse.SalesQty;
                    CommandObj.Parameters.Add("@tradePrice", SqlDbType.Decimal);
                    CommandObj.Parameters["@tradePrice"].Value = salesDtlse.TradePrice;
                    CommandObj.Parameters.Add("@mrp", SqlDbType.Decimal);
                    CommandObj.Parameters["@mrp"].Value = salesDtlse.Mrp;
                    CommandObj.Parameters.Add("@totalMRP", SqlDbType.Decimal);
                    CommandObj.Parameters["@totalMRP"].Value = salesDtlse.TotalMrp;
                    CommandObj.Parameters.Add("@discountPer", SqlDbType.Decimal);
                    CommandObj.Parameters["@discountPer"].Value = salesDtlse.DiscountPer;
                    CommandObj.Parameters.Add("@discountAmt", SqlDbType.Decimal);
                    CommandObj.Parameters["@discountAmt"].Value = salesDtlse.DiscountAmt;
                    CommandObj.Parameters.Add("@salesFreeQty", SqlDbType.Int);
                    CommandObj.Parameters["@salesFreeQty"].Value = salesDtlse.SalesFreeQty;
                    CommandObj.ExecuteNonQuery();
                    ConnectionObj.Close();
                }
                
            }
            catch (Exception ex)
            {
                throw new Exception("Sales Info couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public void SaveaSales(Sales aSales)
        {
            try
            {
                ConnectionObj.Open();
                string query1 = String.Format("INSERT INTO PosSales VALUES(@salesNo,@salesDate,@totalNetAmount,@customerId)");
                CommandObj.CommandText = query1;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@salesNo", SqlDbType.VarChar);
                CommandObj.Parameters["@salesNo"].Value = aSales.SalesNo;
                CommandObj.Parameters.Add("@salesDate", SqlDbType.VarChar);
                CommandObj.Parameters["@salesDate"].Value = aSales.SalesDate;
                CommandObj.Parameters.Add("@totalNetAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@totalNetAmount"].Value = aSales.TotalNetAmount;
                CommandObj.Parameters.Add("@customerId", SqlDbType.VarChar);
                CommandObj.Parameters["@customerId"].Value = aSales.ACustomer.CustomerId;                       
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();             
            }
            catch (Exception ex)
            {
                throw new Exception("Error in data");
            }
        }

        public void SaveCustomerPayment(CustomerPayment aCustomerPayment)
        {
            try
            {
                ConnectionObj.Open();
                string query2 = String.Format("INSERT INTO PosCustomerPayment VALUES(@salesId,@paymentDate,@paidAmount)");
                CommandObj.CommandText = query2;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@salesId", SqlDbType.Int);
                CommandObj.Parameters["@salesId"].Value = aCustomerPayment.ASales.SalesId;
                CommandObj.Parameters.Add("@paymentDate", SqlDbType.VarChar);
                CommandObj.Parameters["@paymentDate"].Value = aCustomerPayment.PaymentDate;
                CommandObj.Parameters.Add("@paidAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@paidAmount"].Value = aCustomerPayment.PaidAmount;
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error in data");
            }
        }

        public List<SalesDtls> GetAllSales()
        {
            aSales = new SalesDtls();
            saless = new List<SalesDtls>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosSales");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    //aSales.SalesId = Convert.ToInt32(reader["salesId"]);
                    //aSales.SalesInvoiceNo = reader["salesNo"].ToString();
                    //aSales.SalesDate = (DateTime)reader["salesDate"];
                    //aSales.SalesType = Convert.ToInt32(reader["salesType"]);
                    //aSales.ProductId = Convert.ToInt32(reader["producId"]);
                    //aSales.SoldQty = Convert.ToInt32(reader["soldQty"]);
                    //aSales.SalesUnitPrice = Convert.ToInt32(reader["salesUnitPrice"]);
                    //aSales.DiscountPer = Convert.ToInt32(reader["discountPer"]);
                    //aSales.DiscountAmt = Convert.ToInt32(reader["discountAmt"]);
                    //aSales.SoldAmount = Convert.ToInt32(reader["soldAmount"]);
                    saless.Add(aSales);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Sales loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return saless;
        }
        public SalesDtls FindSalesDtls(int salesId)
        {
            try
            {
                string search = String.Format("Select * FROM PosSalesDtls WHERE salesId=@SalesDtlsId");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@SalesDtlsId", SqlDbType.Int);
                CommandObj.Parameters["@SalesDtlsId"].Value = salesId;
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                aSales = new SalesDtls();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        //aSales.SalesId = Convert.ToInt32(reader["salesId"]);
                        //aSales.SalesInvoiceNo = reader["salesNo"].ToString();
                        //aSales.SalesDate = (DateTime)reader["salesDate"];
                        //aSales.SalesType = Convert.ToInt32(reader["salesType"]);
                        //aSales.ProductId = Convert.ToInt32(reader["producId"]);
                        //aSales.SoldQty = Convert.ToInt32(reader["soldQty"]);
                        //aSales.SalesUnitPrice = Convert.ToInt32(reader["salesUnitPrice"]);
                        //aSales.DiscountPer = Convert.ToInt32(reader["discountPer"]);
                        //aSales.DiscountAmt = Convert.ToInt32(reader["discountAmt"]);
                        //aSales.SoldAmount = Convert.ToInt32(reader["soldAmount"]);
                    }
                }
                return aSales;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found SalesDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public string NextID(string yearAndMonth)
        {
            try
            {
                var table = new DataTable();
                string sql = "Select max(salesNo) From PosSales";
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = sql;
                ConnectionObj.Open();
                SqlDataAdapter ad = new SqlDataAdapter(CommandObj);
                ad.Fill(table);
                string id = table.Rows[0][0].ToString();
                if (id == "")
                {
                    string dd = "000001";
                    return yearAndMonth + dd;
                }
                else
                {
                    string temp;
                    int Nonzero;
                    int length;
                    string currentMaxId = table.Rows[0][0].ToString().Trim();
                    string lastSixDigit = currentMaxId.Substring(6, 6);
                    temp = lastSixDigit.ToString().Trim();
                    Nonzero = Convert.ToInt32(lastSixDigit.ToString().Trim());
                    int ssss = Nonzero + 1;
                    length = temp.Length - ssss.ToString().Length;
                    string mo = (temp.Substring(0, length) + ssss.ToString());
                    //temp = table.Rows[0][0].ToString().Trim();
                    //Nonzero = Convert.ToInt32(table.Rows[0][0].ToString().Trim());
                    //int ssss = Nonzero + 1;
                    //length = temp.Length - ssss.ToString().Length;
                    //string mo = (temp.Substring(0, length) + ssss.ToString());
                    return yearAndMonth + mo;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Cann't Create Next Id", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public string GetProductNameAutoCompleteData(int categoryId)
        {
            string suggestionList = "";
            try
            {
                string queryString = string.Format("SELECT productBrandName  FROM  PosProduct Where categoryId={0}", categoryId);

                CommandObj.Connection = ConnectionObj;
                CommandObj.CommandText = queryString;
                ConnectionObj.Open();
                SqlDataReader reader = CommandObj.ExecuteReader();

                while (reader.Read())
                {
                    if (string.IsNullOrEmpty(suggestionList))
                    {
                        suggestionList += "\"" + reader["productBrandName"].ToString() + "\"";
                    }
                    else
                    {
                        suggestionList += ", \"" + reader["productBrandName"].ToString() + "\"";
                    }
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Couldn't Loaded Data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return suggestionList;
        }

        internal int ReturnSalesId(string salesNo)
        {
            int id = 0;
            try
            {
                string search = String.Format("Select * FROM PosSales WHERE salesNo='{0}'",salesNo);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                    while (reader.Read())
                    {
                        id =Convert.ToInt32(reader[0]);
                    }

                return id;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found SalesDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }


        public void SaveCustomer(Customer customer)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText =
                    string.Format(
                        "insert into PosCustomer values(@CustomerCode,@CustomerFullName,@CustomerPhone,@CustomerEmail)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@CustomerCode", SqlDbType.VarChar).Value = customer.CustomerCode;
                CommandObj.Parameters.Add("@CustomerFullName", SqlDbType.VarChar).Value = customer.CustomerFullName;
                CommandObj.Parameters.Add("@CustomerPhone", SqlDbType.VarChar).Value = customer.CustomerPhone;
                CommandObj.Parameters.Add("@CustomerEmail", SqlDbType.VarChar).Value = customer.CustomerEmail;
                CommandObj.ExecuteNonQuery();

            }
            catch (Exception exception)
            {

                throw new Exception("Could Not save customer data", exception);
            }
            finally
            {
                ConnectionObj.Close();
            }


        }

        public int GetCustomerId(string customerCode)
        {
            try
            {
                int customerId = 0;
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("select Id from  PosCustomer where CustomerCode='{0}'",
                    customerCode);
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    customerId = Convert.ToInt32(reader[0]);

                }
                return customerId;

            }
            catch (Exception exception)
            {

                throw new Exception("Customer Id does not found!", exception);
            }

            finally
            {
                ConnectionObj.Close();
            }
        }


        //public Customer GetCustomer(int customerId)
        //{
        //    try
        //    {
        //        ConnectionObj.Open();
        //        CommandObj.CommandText = string.Format("select CustomerId,CustomerCode,CustomerFullName from PosCustomer where CustomerId='{0}'",customerId);
        //        SqlDataReader reader = CommandObj.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            Customer customer=new Customer();
        //            customer.CustomerCode = reader[1].ToString();
        //            customer.CustomerFullName = reader[2].ToString();
        //        }

        //        return
        //    }
        //    catch (Exception)
        //    {
                
        //        throw;
        //    }
        //}

    }
}